package arquitetura.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import to.Genero;
import conexao.Conexao;
import conexao.ConexaoImpl;
import exceptions.*;

public class ModelGeneroImpl implements ModelGenero{
	private Conexao conexao;
	
	public ModelGeneroImpl(){
		conexao = ConexaoImpl.getInstancia();
    }
	
	public void incluir(Genero genero) throws ConexaoException, ModelException{
        Connection conex = conexao.conectar();
        int ultimoCodigo = conexao.ultimoCodigo("Genero");
        
        String sql = "INSERT INTO Genero(codigo, descricao) " + "VALUES(?,?)";
        try {
            PreparedStatement pstm = conex.prepareStatement(sql);
            pstm.setInt(1, ultimoCodigo + 1);
            pstm.setString(2, genero.getDescricao());
            pstm.executeUpdate();
            pstm.close();
        } catch (SQLException e) {
            throw new ModelException(e.getMessage());
        }finally{
        	conexao.desconectar(conex);
        }
    }
	
	public void alterar(Genero genero) throws ConexaoException, ModelException{		
		Connection conex = conexao.conectar();
	        
	    String sql = "UPDATE Genero SET descricao=?" + "WHERE codigo=?";
	    try {
	        PreparedStatement pstm = conex.prepareStatement(sql);	            
	        pstm.setString(1, genero.getDescricao());
	        pstm.setInt(2, genero.getCodigo());
	        pstm.executeUpdate();
	        pstm.close();
	    } catch (SQLException e) {
	        throw new ModelException(e.getMessage());
	    }finally{
	    	conexao.desconectar(conex);
	    }
	}
	
	public void excluir(int codigo)throws ConexaoException, ModelException{		
		Connection conex = conexao.conectar();		
		String sql = "DELETE FROM Genero WHERE codigo=?";
		try{
			PreparedStatement pstm = conex.prepareStatement(sql);
            pstm.setInt(1, codigo);
            pstm.executeUpdate();
            pstm.close();
        } catch (SQLException e) {
            throw new ModelException(e.getMessage());
        }finally{
        	conexao.desconectar(conex);
        }
	}
	
	public Genero consultar(int codigo) throws ConexaoException, ModelException{
		Genero genero = null;
		
		Connection conex = conexao.conectar();		
		String sql = "SELECT * FROM Genero WHERE codigo = ?";
		
		try{
			PreparedStatement pstm = conex.prepareStatement(sql);
			pstm.setInt(1, codigo);
            ResultSet rs = pstm.executeQuery();
		
			if (rs.next()) {
				genero = new Genero();
				
				genero.setCodigo(rs.getInt("codigo"));
				genero.setDescricao(rs.getString("descricao"));			
			}
			return genero;
	    }catch(SQLException e){
	        throw new ModelException(e.getMessage());
	    }finally{
	    	conexao.desconectar(conex);
	    }
	}
	
	public Collection<String []> listarParaGrid() throws ConexaoException, ModelException {
		Collection<String []> lista = new ArrayList<String []>();
		
		Connection conex = conexao.conectar();		
		String sql = "SELECT Codigo, Descricao " +
					 "FROM Genero " +
					 "ORDER BY Descricao";
		try{
			PreparedStatement stm = conex.prepareStatement(sql);
            ResultSet rs = stm.executeQuery();
            
            while(rs.next())
            	lista.add(new String[] {rs.getString("codigo"), rs.getString("descricao")});
            
			return lista;
	    }catch(SQLException e){
	        throw new ModelException(e.getMessage());
	    }finally{
	    	conexao.desconectar(conex);
	    }
	}
	
	public boolean descricaoJahCadastrada(Genero genero) throws ConexaoException, ModelException{
		Connection conex = conexao.conectar();
		boolean jahExisteRegistro = false;
		int codigo = genero.getCodigo();
		String descricao = genero.getDescricao();
		String sql = "";
        
		if (codigo == 0)
			sql = "SELECT Codigo FROM Genero WHERE Descricao is not null AND Descricao = ?";
		else
			sql = "SELECT Codigo FROM Genero WHERE Descricao is not null AND Descricao = ? AND Codigo <> " + codigo;
		
        try {
            PreparedStatement pstm = conex.prepareStatement(sql);
            pstm.setString(1, descricao);
            ResultSet rs = pstm.executeQuery();
            
            jahExisteRegistro = rs.next();
            pstm.close();
            
            return jahExisteRegistro;
        } catch (SQLException e) {
            throw new ModelException(e.getMessage());
        }finally{
        	conexao.desconectar(conex);        	
        }
	}
	
	public boolean podeExcluirGenero(int codigo) throws ConexaoException, ModelException{
		return (! existeFilme(codigo));
	}
	
	private boolean existeFilme(int codigo) throws ConexaoException, ModelException{
		Connection conex = conexao.conectar();
		boolean jahExisteRegistro = false;
		
		String sql = "SELECT Codigo FROM Filme WHERE Genero = " + codigo;		
        try {
            PreparedStatement pstm = conex.prepareStatement(sql);
            ResultSet rs = pstm.executeQuery();
            
            jahExisteRegistro = rs.next();
            pstm.close();
            
            return jahExisteRegistro;
        } catch (SQLException e) {
            throw new ModelException(e.getMessage());
        }finally{
        	conexao.desconectar(conex);     
        }
	}
}
